package www.xfOnline.BAO;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Properties;
/**
 * BaseDAO：封装了JDBC的常用CRUD操作
 *
 */
public class JDBCDAO {
	Connection conn;
	Statement stmt;
	PreparedStatement ps;
	ResultSet rs;
	static String url, user, password, driver;

	static {
		try {
			Properties pro = new Properties();
			pro.load(JDBCDAO.class.getResourceAsStream("/db.properties"));
			url = pro.getProperty("url");
			user = pro.getProperty("user");
			password = pro.getProperty("password");
			//driver = pro.getProperty("driver");

			Class.forName("com.mysql.jdbc.Driver");
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

	public Connection getConnection() {
		try {
			conn = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 增删改
	 * @param sql
	 * @param params
	 * @return int
	 */
	public int update(String sql, Object[] params) {
		int rows = 0;
		try {
			getConnection();
			ps = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rows = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			close();
		}
		return rows;
	}
	
	/**
	 * 查询多条记录
	 * @param sql
	 * @param params
	 * @return ArrayList<HashMap<String, Object>>
	 */
	public ArrayList<HashMap<String, Object>> findAll(String sql, Object[] params) {
		ArrayList<HashMap<String, Object>> list = null;
		try {
			getConnection();
			ps = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();
			list = new ArrayList<HashMap<String, Object>>();
			HashMap<String, Object> map = null;
			ResultSetMetaData md = rs.getMetaData();// 获取记录集的元数据
			int columnCount = md.getColumnCount();// 列数
			while (rs.next()) {
				map = new HashMap<String, Object>(columnCount);
				for (int i = 1; i <= columnCount; i++) {
					map.put(md.getColumnName(i), rs.getObject(i));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			close();
		}
		return list;

	}
	
	/**查询单条记录
	 * @param sql
	 * @param params
	 * @return HashMap<String, Object>
	 */
	public HashMap<String, Object> findOne(String sql, Object[] params) {
		HashMap<String, Object> obj = new HashMap<String, Object>();
		try {
			getConnection();
			ps = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData md = rs.getMetaData();
			int columnCount = md.getColumnCount();
			if(rs.next()){
				for (int i = 1; i <= columnCount; i++) {
					obj.put(md.getColumnName(i), rs.getObject(i));
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			close();
		}
		
		return obj;
	}
	
	/**
	 * 查询多条记录
	 * @param <T>
	 * @param sql
	 * @param params
	 * @return ArrayList<HashMap<String, Object>>
	 */
	public <T> ArrayList<T> findAll(String sql, Object[] params,Class<T> cls) {
		ArrayList<T> resultList = new ArrayList<T>();
		T resultObj = null;
		//HashMap<String, Object> obj = new HashMap<String, Object>();
		try {
			getConnection();
			ps = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData md = rs.getMetaData();// 获取记录集的元数据
			int columnCount = md.getColumnCount();// 列数
			while(rs.next()){
				resultObj = cls.newInstance();
				for (int i = 1; i <= columnCount; i++) {
				//	obj.put(md.getColumnName(i), rs.getObject(i));
					String colName = md.getColumnName(i);
					Object colValue = rs.getObject(i);
					
					//获取bean的所有字段,并且绑定值
					Field field = cls.getDeclaredField(colName);
					field.setAccessible(true);
					
					field.set(resultObj, colValue);
				}
				resultList.add(resultObj);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			close();
		}
		return resultList;

	}
	
	/**查询单条记录
	 * @param sql
	 * @param params
	 * @param cls
	 * @return <T>T
	 */
	public <T>T findOne(String sql, Object[] params, Class<T> cls) {
		T resultObj = null;
		//HashMap<String, Object> obj = new HashMap<String, Object>();
		try {
			getConnection();
			ps = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData md = rs.getMetaData();
			int columnCount = md.getColumnCount();
			if(rs.next()){
				resultObj = cls.newInstance();
				for (int i = 1; i <= columnCount; i++) {
					//obj.put(md.getColumnName(i), rs.getObject(i));
					String colName = md.getColumnName(i);
					Object colValue = rs.getObject(i);
					
					//获取bean的所有字段,并且绑定值
					Field field = cls.getDeclaredField(colName);
					field.setAccessible(true);
					
					field.set(resultObj, colValue);
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			close();
		}
		return resultObj;
	}
	
	public ArrayList<Object[]> findAll(String sql, Object[] params, int column) {
		ArrayList<Object[]> result = new ArrayList<Object[]>();
		
		try {
			getConnection();
			ps = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData md = rs.getMetaData();// 获取记录集的元数据
			int columnCount = md.getColumnCount();// 列数
			while (rs.next()) {
				Object[] obj = new Object[column];
				for (int i = 1; i <= columnCount; i++) {
					obj[i-1]=rs.getObject(i);
				}
				result.add(obj);
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			close();
		}
		System.out.println(result.size()+"----1");
		return result;
	}
	
/*	public <T> int update(String sql, Object[] params,Class<T> cls) {
		int rows = 0;
		try {
			getConnection();
			ps = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rows = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO: handle exception
			//e.printStackTrace();
		} finally {
			close();
		}
		return rows;
	}
	
*/
	/**
	 * 关闭连接,释放资源
	 */
	public void close(){
		try {
			if(ps!=null) {
				ps.close();
				ps = null;
			}
			if(rs!=null) {
				rs.close();
				rs = null;
			}
			if(conn!=null) {
				conn.close();
				conn = null;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		
	}

}